using System.Collections.Generic;
using Microsoft.Data.Sqlite;
using 第六次作业.entity;

namespace 第六次作业.utils.crud;

public class StudentRepository
{
    public void Create(Student student)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            List<Student> students = new List<Student>();
            students = GetAll(student.ClassId);
            int num = students.Count;
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "INSERT INTO Student (Id, Name, ClassId) VALUES (@Id, @Name, @ClassId)";
            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);
            command.Parameters.AddWithValue("@Id", num + 1);
            command.ExecuteNonQuery();
        }
    }

    public void Delete(int id)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "DELETE FROM Student WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);
            command.ExecuteNonQuery();
        }
    }

    public void Update(Student student)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "UPDATE Student SET Name = @Name, ClassId = @ClassId WHERE Id = @Id";
            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);
            command.Parameters.AddWithValue("@Id", student.Id);
            command.ExecuteNonQuery();
        }
    }

    public List<Student> GetAll(int classId)
    {
        List<Student> students = new List<Student>();
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Student WHERE ClassId = @ClassId";
            command.Parameters.AddWithValue("@ClassId", classId);
            SqliteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Student student = new Student
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    ClassId = reader.GetInt32(2)
                };
                students.Add(student);
            }
        }
        return students;
    }
    
    public void DeleteByClassId(int classId)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "DELETE FROM Student WHERE ClassId = @ClassId";
            command.Parameters.AddWithValue("@ClassId", classId);
            command.ExecuteNonQuery();
        }
    }
    
    public Student GetById(int id)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Student WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);
            SqliteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Student student = new Student
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    ClassId = reader.GetInt32(2)
                };
                
                return student;
            }
        }
        
        return null;
    }
}